package net.fenghaitao.utils;

import net.fenghaitao.domain.BlockNameResolver;
import net.fenghaitao.enums.AggregateType;
import net.fenghaitao.enums.AutoExcelCellType;
import net.fenghaitao.managers.*;
import net.fenghaitao.parameters.TemplatePara;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.*;

import javax.validation.constraints.NotNull;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class CellUtil {

    private static final String regName = "'?%s'?!\\$([a-z]+)\\$([0-9]+)";

    /**
     * Set the value of the corresponding type, according to different parameter types
     */
    public static void setValue(Cell cell, Object value) {
        if (value == null)
            return;

        if (value instanceof Boolean) {
            cell.setCellValue((boolean) value);
        } else if (value instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) value).doubleValue());
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
            Workbook workbook = cell.getSheet().getWorkbook();
            CellStyle dateStyle = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();
            dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
            cell.setCellStyle(dateStyle);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    /**
     * Get the value of any type of cell
     */
    public static Object getValue(Cell cell) {
        return getValue(cell, cell.getCellType());
    }

    private static Object getValue(Cell cell, CellType type) {
        switch (type) {
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case ERROR:
                return ErrorEval.getText(cell.getErrorCellValue());
            case FORMULA:
                return getValue(cell, cell.getCachedFormulaResultType());
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    return dateFormat.format(cell.getDateCellValue());
                } else {
                    return cell.getNumericCellValue();
                }
            case STRING:
                String str = cell.getStringCellValue();
                if (str != null && !str.isEmpty())
                    return str;
                else
                    return null;
            case _NONE:
            case BLANK:
            default:
                return null;
        }
    }

    private static String[] getAttrArr(@NotNull String str){
        return AutoExcelCellType.DEFAULT.getArr(str);
    }
    private static String[] getFormulaArr(@NotNull String str){
        return AutoExcelCellType.FORMULA.getArr(str);
    }

    /**
     * Resolve all of the cell name in the workbook
     */
    public static <T extends TemplatePara> List<BlockNameResolver<T>> resolveCellNames(Workbook workbook, List<T> paras) {
        //find out all cell name in the template and sort them
        List<Name> names = new ArrayList<>(workbook.getAllNames());
        Set<String> dataSourceNameSet = paras.stream().map(it->it.getDataSourceName().toLowerCase()).collect(Collectors.toSet());
        Map<String,List<Name>> nameMap = names.stream()
                .filter(it->!it.isDeleted()).collect(Collectors.groupingBy(it->
                        getAttrArr(it.getNameName())[0].toLowerCase()
                ));
        List<BlockNameResolver<T>> blockNameResolvers = new ArrayList<>();

        BlockNameResolver<T> tmpBlockNameResolver;
        Sheet sheet ;
        Pattern pattern ;
        //Iterate over all cell names, categorize by data source name, and resolve the column and row indexes corresponding to cell names
        for (String key : nameMap.keySet()) {
            if(!dataSourceNameSet.contains(key)){
                continue;
            }
            List<Name> nameList = nameMap.get(key);
            tmpBlockNameResolver = new BlockNameResolver<>();
            T t = paras.stream().filter(it -> it.getDataSourceName().equalsIgnoreCase(key)).findFirst().get();
            String sheetName = nameList.get(0).getSheetName();
            sheet = workbook.getSheet(sheetName);
            tmpBlockNameResolver.setPara(t);
            tmpBlockNameResolver.setDataSourceName(key.toLowerCase());
            tmpBlockNameResolver.setOriginalDataSourceName(key);
            tmpBlockNameResolver.setSheetName(sheetName);
            tmpBlockNameResolver.setSheet(sheet);
            blockNameResolvers.add(tmpBlockNameResolver);
            String regex = String.format(regName, Pattern.quote(sheetName));
            pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
            getBlockNameResolver(tmpBlockNameResolver,nameList,pattern,sheet);
        }

        return blockNameResolvers;
    }

    private static <T> void getBlockNameResolver(BlockNameResolver<T> tmpBlockNameResolver,List<Name> nameList,Pattern pattern,Sheet sheet){
        for (Name name : nameList) {
            String cellName = name.getNameName();
            // 获取公式
            String[] formulaArr = getFormulaArr(cellName);
            String[] arr = getAttrArr(formulaArr[0]);
            if (arr.length < 2 && formulaArr.length < 2) continue;
            Matcher matcher = pattern.matcher(name.getRefersToFormula());
            if (!matcher.matches()) continue;
            int rowIndex = Integer.parseInt(matcher.group(2)) - 1;
            int colIndex = colNameToIndex(matcher.group(1));
            Cell cell = SheetUtil.getOrCreateCell(sheet, rowIndex, colIndex);
            String secondPart = arr[arr.length-1];
            // 存在公式的情况
            if (formulaArr.length == 2) {
                putFormulaCell(formulaArr, cell, cellName, rowIndex, colIndex, tmpBlockNameResolver, secondPart);
            }else{
                if (AutoExcelCellType.ROW_NO.getTypeName().equalsIgnoreCase(secondPart)) {
                    RowNoCellManager rowNoCellManager = RowNoCellManager.builder()
                            .cellName(cellName).rowIndex(rowIndex).cellStyle(cell.getCellStyle())
                            .colIndex(colIndex)
                            .build();
                    tmpBlockNameResolver.setRowNoCellManager(rowNoCellManager);
                } else {
                    CellManager cellManager = CellManager.builder()
                            .cellName(cellName).rowIndex(rowIndex).cellStyle(cell.getCellStyle())
                            .colIndex(colIndex).colName(matcher.group(1))
                            .build();
                    String[] secondSplit = getAttrArr(secondPart);
                    if(secondSplit.length == 2){
                        // 嵌套表情况
                        String secondStr = secondSplit[1];
                        BlockNameResolver childName = tmpBlockNameResolver.getChildBlockNameResolver();
                        if(Objects.isNull(childName)){
                            childName = new BlockNameResolver<T>();
                            tmpBlockNameResolver.getFieldNameCells().put(secondSplit[0], cellManager);
                            tmpBlockNameResolver.getFieldNameMap().put(secondSplit[0],secondSplit[0]);
                            tmpBlockNameResolver.setChildBlockNameResolver(childName);
                            childName.setIsChild(true);
                            childName.setDataSourceName(secondSplit[0].toLowerCase());
                            childName.setOriginalDataSourceName(secondSplit[0]);
                            childName.setSheetName(sheet.getSheetName());
                            childName.setSheet(sheet);
                        }
                        childName.getFieldNameCells().put(secondStr, cellManager);
                        childName.getFieldNameMap().put(secondStr, secondStr);
                    }else{
                        tmpBlockNameResolver.getFieldNameCells().put(secondPart, cellManager);
                        tmpBlockNameResolver.getFieldNameMap().put(secondPart, arr[1]);
                    }

                }
            }
        }
    }

    private static void putFormulaCell(String[] formulaArr,Cell cell,String cellName,int rowIndex,int colIndex,BlockNameResolver tmpBlockNameResolver,String secondPart) {
        if (formulaArr[0].endsWith(AutoExcelCellType.FORMULA.getTypeName())) {
            if (cell.getCellType() == CellType.FORMULA) {
                FormulaCellManager formulaCellManager = FormulaCellManager.builder()
                        .cellName(cellName).rowIndex(rowIndex).cellStyle(cell.getCellStyle())
                        .colIndex(colIndex).formula(cell.getCellFormula())
                        .build();
                tmpBlockNameResolver.getFormulaCellManagers().add(formulaCellManager);
            }
        }  else {
            AggregateType aggregateType = AggregateType.NONE;
            try {
                aggregateType = AggregateType.valueOf(formulaArr[1].toUpperCase());
            } catch (Exception ex) {
                //do nothing
            }
            if (aggregateType != AggregateType.NONE) {
                AggregateCellManager aggregateCellManager = AggregateCellManager.builder()
                        .cellName(cellName).rowIndex(rowIndex)
                        .colIndex(colIndex).aggregateType(aggregateType)
                        .build();
                tmpBlockNameResolver.getFieldNameAggregateCells().put(secondPart, aggregateCellManager);
            }
        }
    }

    /**
     * Converts the column name to the index location，eg.AB -> 27
     */
    private static int colNameToIndex(String chars) {
        if (chars.isEmpty())
            return -1;

        int index = 0;
        int place = chars.length();
        int tmp = 0;

        for (int i = 0; i < chars.length(); ++i) {
            tmp = Convert.charToNum(chars.charAt(i));
            if (place > 1)
                index += 26 * (place - 1) * (tmp + 1);
            else
                index += tmp;

            --place;
        }
        return index;
    }


}
